package org.lq.classinfo.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.classinfo.dao.TeacherInfoDao;
import org.lq.classinfo.entity.TeacherInfo;
import org.lq.util.CastUtil;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.List;

/**
 * 教师信息数据访问实现层
 * @author 张三
 * @create 2020-10-13 18:32
 */
@Log4j
public class TeacherInfoDaoImpl implements TeacherInfoDao {

    /**
     * 添加教师信息
     * @param teacherInfo
     * @return
     */
    @Override
    public int save(TeacherInfo teacherInfo) {
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).update("insert into staff_info " +
                    "(role_id, staff_name, staff_sex, staff_age, staff_native_place, staff_idcard, staff_brithday, " +
                    "staff_office_phone, staff_mobile_phone, staff_eamil, staff_addr, staff_qq, staff_entry_time, " +
                    "staff_eduction_level, staff_remark, staff_state, user_number, user_passowrd) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
                    teacherInfo.getRoleId(),teacherInfo.getStaffName(),teacherInfo.getStaffSex(),teacherInfo.getStaffAge(),
                    teacherInfo.getStaffNativePlace(),teacherInfo.getStaffIdcard(),teacherInfo.getStaffBrithday(),teacherInfo.getStaffOfficePhone(),
                    teacherInfo.getStaffMobilePhone(),teacherInfo.getStaffEamil(),teacherInfo.getStaffAddr(),teacherInfo.getStaffQq(),
                    teacherInfo.getStaffEntryTime(), teacherInfo.getStaffEductionLevel(), teacherInfo.getStaffRemark(),
                    teacherInfo.getStaffState(),teacherInfo.getUserNumber(),teacherInfo.getUserPassowrd());
        } catch (SQLException e) {
            log.error("数据访问实现层->save错误"+e);
        }
        return 0;
    }

    /**
     * 修改教师信息
     * @param teacherInfo
     * @return
     */
    @Override
    public int update(TeacherInfo teacherInfo) {
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).update("update staff_info set " +
                    "staff_name = ?," +
                    "staff_sex = ?," +
                    "staff_age=?," +
                    "staff_native_place=?," +
                    "staff_idcard=?," +
                    "staff_brithday=?," +
                    "staff_office_phone=?," +
                    "staff_mobile_phone=?," +
                    "staff_eamil=?," +
                    "staff_addr=?," +
                    "staff_qq=?," +
                    "staff_entry_time=?," +
                    "staff_eduction_level=?," +
                    "staff_remark=?," +
                    "staff_state=?," +
                    "user_number=?," +
                    "user_passowrd=?" +
                    " where staff_id=?;",
                    teacherInfo.getStaffName(),
                    teacherInfo.getStaffSex(),
                    teacherInfo.getStaffAge(),
                    teacherInfo.getStaffNativePlace(),
                    teacherInfo.getStaffIdcard(),
                    teacherInfo.getStaffBrithday(),
                    teacherInfo.getStaffOfficePhone(),
                    teacherInfo.getStaffMobilePhone(),
                    teacherInfo.getStaffEamil(),
                    teacherInfo.getStaffAddr(),
                    teacherInfo.getStaffQq(),
                    teacherInfo.getStaffEntryTime(),
                    teacherInfo.getStaffEductionLevel(),
                    teacherInfo.getStaffRemark(),
                    teacherInfo.getStaffState(),
                    teacherInfo.getUserNumber(),
                    teacherInfo.getUserPassowrd(),
                    teacherInfo.getStaffId());
        } catch (SQLException e) {
            log.error("数据访问实现层->update错误"+e);
        }
        return 0;
    }

    /**
     * 根据教师编号删除教师信息
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).update("delete from staff_info where staff_id = ?",id);
        } catch (SQLException e) {
            log.error("数据访问实现层->delete错误"+e);
        }
        return 0;
    }

    /**
     * 根据编号查询教师信息
     * @param id
     * @return
     */
    @Override
    public TeacherInfo getById(int id) {
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_staff_info where staffId = ? and roleId = 4",
                    new BeanHandler<TeacherInfo>(TeacherInfo.class), id);
        } catch (SQLException e) {
            log.error("数据访问实现层->getById错误"+e);
        }
        return null;
    }

    /**
     * 查询教师信息总行数
     * @return
     */
    @Override
    public int getCount() {
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_staff_info where roleId = 4",
                    new ScalarHandler<Long>()).intValue();
        } catch (SQLException e) {
            log.error("数据访问实现层->getCount()错误"+e);
        }
        return 0;
    }

    /**
     * 分页查询教师信息
     * @param startIndex 开始位置
     * @param pageSize 页面大小
     * @return
     */
    @Override
    public List<TeacherInfo> pageList(int startIndex, int pageSize) {
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_staff_info where roleId = 4 limit ?,?",
                    new BeanListHandler<TeacherInfo>(TeacherInfo.class), startIndex, pageSize);
        } catch (SQLException e) {
            log.error("数据访问实现层->pageList错误"+e);
        }
        return null;
    }

    @Override
    public int getCount(String... values) {
        return 0;
    }

    @Override
    public List<TeacherInfo> pageByValues(int startIndex, int pageSize, String... value) {
        return null;
    }

    /**
     * 根据姓名查询总行数
     * @param name 姓名关键字
     * @return
     */
    @Override
    public int getCountByName(String name) {
        String key = "%"+name+"%";
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_staff_info " +
                    "where staffName like ? and roleId = 4", new ScalarHandler<Long>(), key).intValue();
        } catch (SQLException e) {
            log.error("数据访问实现层->getCountByName错误"+e);
        }
        return 0;
    }

    /**
     * 根据性别查询总行数
     * @param sex 性别关键字
     * @return
     */
    @Override
    public int getCountBySex(String sex) {
        String key = "%"+sex+"%";
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_staff_info " +
                    "where staffSex like ? and roleId = 4", new ScalarHandler<Long>(), key).intValue();
        } catch (SQLException e) {
            log.error("数据访问实现层->getCountBySex错误"+e);
        }
        return 0;
    }

    /**
     * 根据姓名查找教师信息
     * @param startIndex 开始位置
     * @param pageSize 页面大小
     * @param name
     * @return
     */
    @Override
    public List<TeacherInfo> pageByName(int startIndex, int pageSize, String name) {
        String key = "%"+name+"%";
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_staff_info where staffName like ? and roleId = 4 limit ?,?",
                    new BeanListHandler<TeacherInfo>(TeacherInfo.class), key, startIndex, pageSize);
        } catch (SQLException e) {
            log.error("数据访问实现层->pageByName错误"+e);
        }
        return null;
    }

    /**
     * 根据性别查找教师信息
     * @param startIndex 开始位置
     * @param pageSize 页面大小
     * @param sex
     * @return
     */
    @Override
    public List<TeacherInfo> pageBySex(int startIndex, int pageSize, String sex) {
        String key = "%"+sex+"%";
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_staff_info where staffSex like ? and roleId = 4 limit ?,?",
                    new BeanListHandler<TeacherInfo>(TeacherInfo.class), key, startIndex, pageSize);
        } catch (SQLException e) {
            log.error("数据访问实现层->pageBySex错误"+e);
        }
        return null;
    }
}
